/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.app.income.repository;

import me.zhengjie.modules.app.income.domain.IncomeInfo;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
* @website https://el-admin.vip
* @author Haojie Li
* @date 2021-03-02
**/
public interface IncomeInfoRepository extends JpaRepository<IncomeInfo, String>, JpaSpecificationExecutor<IncomeInfo> {

	@Query(value = "SELECT a.bind_id,a.hotel_id,a.hotel_name,a.hotel_room_id,a.hotel_room_no,a.ip,a.mac,SUM(a.time_result) AS time_result ,a.belong_date FROM app_record_info a WHERE a.detail_id = :detailId and is_enable = 1 GROUP BY a.belong_date",nativeQuery = true)
	List<Map<String,Object>> findInComeByDetailId(@Param("detailId") String detailId);

	/**
	 * 查询酒店每个月的收益
	 * @param startBelongDate
	 * @param endBelongDate
	 * @return
	 */
//	  " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " 判断值是否为空
	@Query(value = "SELECT bind_id AS bindId,hotel_id AS hotelId ,hotel_name AS hotelName ,hotel_room_id AS hotelRoomId ,hotel_room_no AS hotelRoomNo , SUM(income_money) AS incomeMoney, DATE_FORMAT(belong_date,'%Y-%m') AS belongDate  FROM app_income_info WHERE if(:startBelongDate = '' OR :startBelongDate is null , 1=1, belong_date >= :startBelongDate)  AND if(:endBelongDate = '' OR :endBelongDate is null , 1=1,belong_date <= :endBelongDate)  AND if(:hotelIds is null , 1=1,  FIND_IN_SET (hotel_id,:hotelIds)) GROUP BY hotel_id,belongDate" , nativeQuery =  true)
	Page<Map<String,Object>> findIncomeBetweenDate(LocalDate startBelongDate, LocalDate endBelongDate, String hotelIds, Pageable pageable);

	@Query(value = "SELECT bind_id AS bindId,hotel_id AS hotelId ,hotel_name AS hotelName ,hotel_room_id AS hotelRoomId ,hotel_room_no AS hotelRoomNo , SUM(income_money) AS incomeMoney, DATE_FORMAT(belong_date,'%Y-%m') AS belongDate  FROM app_income_info WHERE if(:startBelongDate = '' OR :startBelongDate is null , 1=1, belong_date >= :startBelongDate)  AND if(:endBelongDate = '' OR :endBelongDate is null , 1=1,belong_date <= :endBelongDate) AND if(:hotelIds is null , 1=1,  FIND_IN_SET (hotel_id,:hotelIds)) and is_close = :isClose GROUP BY hotel_id,belongDate" , nativeQuery =  true)
	Page<Map<String,Object>> findIncomeBetweenDate(LocalDate startBelongDate, LocalDate endBelongDate,int isClose, String hotelIds, Pageable pageable);

	@Query(value = "SELECT bind_id AS bindId,hotel_id AS hotelId ,hotel_name AS hotelName ,hotel_room_id AS hotelRoomId ,hotel_room_no AS hotelRoomNo , SUM(income_money) AS incomeMoney, DATE_FORMAT(belong_date,'%Y-%m') AS belongDate  FROM app_income_info WHERE belong_date >= :startBelongDate AND belong_date <= :endBelongDate and if(:hotelId = '' OR :hotelId  is null , 1 = 1 , hotel_id = :hotelId) GROUP BY hotel_id" , nativeQuery =  true)
	List<Map<String,Object>> findIncomeBetweenDateAll(LocalDate startBelongDate, LocalDate endBelongDate, String hotelId);

	@Query(value = "SELECT bind_id AS bindId,hotel_id AS hotelId ,hotel_name AS hotelName ,hotel_room_id AS hotelRoomId ,hotel_room_no AS hotelRoomNo , SUM(income_money) AS incomeMoney, DATE_FORMAT(belong_date,'%Y-%m') AS belongDate  FROM app_income_info WHERE belong_date >= :startBelongDate AND belong_date <= :endBelongDate and if(:hotelId = '' OR :hotelId  is null , 1 = 1 , hotel_id = :hotelId) and is_close = :isClose GROUP BY hotel_id" , nativeQuery =  true)
	List<Map<String,Object>> findIncomeBetweenDateAll(LocalDate startBelongDate, LocalDate endBelongDate,int isClose, String hotelId);

	/**
	 * 查询酒店当月每个房间的收益详情
	 * @param startBelongDate
	 * @param endBelongDate
	 * @param hotelId
	 * @param isClose
	 * @return
	 */
	@Query(value = "SELECT *  FROM app_income_info WHERE if(:startBelongDate = '' OR :startBelongDate is null ,1=1, belong_date >= :startBelongDate)  AND if(:endBelongDate = '' OR :endBelongDate is null ,1=1, belong_date <= :endBelongDate) and is_close = :isClose and and hotel_id = :hotelId order by belong_date " , nativeQuery =  true)
	Page<IncomeInfo> findInComeByHotelId(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId,int isClose,Pageable pageable);

//	  " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " 判断值是否为空
	@Query(value = "SELECT *  FROM app_income_info WHERE if(:startBelongDate = '' OR :startBelongDate is null ,1=1, belong_date >= :startBelongDate)  AND if(:endBelongDate = '' OR :endBelongDate is null ,1=1, belong_date <= :endBelongDate) and hotel_id = :hotelId order by belong_date " , nativeQuery =  true)
	Page<IncomeInfo> findInComeByHotelId(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId,Pageable pageable);

	@Modifying
	@Query(value = "update app_income_info set is_close = :isClose WHERE belong_date >= :startBelongDate AND belong_date <= :endBelongDate and hotel_id = :hotelId  " , nativeQuery =  true)
	void updateIsCloseByHotelId(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId, @Param("isClose") int isClose);


	//download
	@Query(value = "SELECT *  FROM app_income_info WHERE belong_date >= :startBelongDate AND belong_date <= :endBelongDate and is_close = :isClose and and hotel_id = :hotelId order by belong_date " , nativeQuery =  true)
	List<IncomeInfo> findInComeByHotelIdDownload(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId,int isClose);

	@Query(value = "SELECT *  FROM app_income_info WHERE belong_date >= :startBelongDate AND belong_date <= :endBelongDate and hotel_id = :hotelId order by belong_date " , nativeQuery =  true)
	List<IncomeInfo> findInComeByHotelIdDownload(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId);

//	@Query(value = "SELECT *  FROM app_income_info WHERE if(?1 = '' OR ?1 is null ,1=1, belong_date >= ?1)  AND if(?2 = '' OR ?2 is null ,1=1, belong_date <= ?2) if(?3 ='' OR ?3 is null,and 1=1,and hotel_id=?3) " , nativeQuery =  true)
//	List<IncomeInfo> findInComeByHotelIdExport(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelId") String hotelId);
//
//	@Query(value = "SELECT *  FROM app_income_info WHERE  if(?1 ='' OR ?1 is null,1=1,hotel_id=?1) order by belong_date " , nativeQuery=true)
//	List<IncomeInfo> findInComeByHotelIdExport1(String hotelId);


	@Query(value = "SELECT *  FROM app_income_info WHERE if(:startBelongDate = '' OR :startBelongDate is null ,1=1, belong_date >= :startBelongDate)  AND if(:endBelongDate = '' OR :endBelongDate is null ,1=1, belong_date <= :endBelongDate) AND if(:hotelIds is null , 1=1, hotel_id in (:hotelIds)) order by belong_date " , nativeQuery =  true)
	List<IncomeInfo> findInComeByHotelIdExport(@Param("startBelongDate")LocalDate startBelongDate, @Param("endBelongDate")LocalDate endBelongDate, @Param("hotelIds") String hotelIds);

}
